from SystematicFactors.General import *
import xlrd
import os

def Load_IPO(database, pathFilename):
    # df_ipo = pd.read_csv(pathFilename, engine="python", header=0).fillna(0) # encoding='gbk',
    # print(df_ipo)
    # date_trans = list(stock_position['Date'])

    wb = xlrd.open_workbook(filename=pathFilename)
    sheet = wb.sheet_by_index(0)  # 通过索引获取表格
    #
    nrows = sheet.nrows
    ncols = sheet.ncols
    documents = []
    headerIndexByName = {}
    for i in range(1, nrows):
        row_data = sheet.row_values(i)
        content = row_data
        print(content)
        #
        if i == 1:
            headerCount = 0
            for header in content:
                header = header.strip(" ")
                header = header.strip("\n")
                headerIndexByName[header] = headerCount
                headerCount = headerCount + 1
            continue
        #
        if content[0] == "":
            break
        #
        document = {}
        document["Symbol"] = row_data[headerIndexByName["代码"]]
        document["Name"] = row_data[headerIndexByName["名称"]]
        # t = row_data[headerIndexByName["招股日期"]]
        # et = Gadget.ParseExcelDatetime(t)
        document["Announce_Date"] = Gadget.ParseExcelDatetime(row_data[headerIndexByName["招股日期"]]).date()
        document["Issue_Date"] = Gadget.ParseExcelDatetime(row_data[headerIndexByName["网上发行日期"]]).date()
        document["IPO_Date"] = Gadget.ParseExcelDatetime(row_data[headerIndexByName["上市日期"]]).date()
        document["IPO_Price"] = row_data[headerIndexByName["发行价格"]]
        document["IPO_PE"] = row_data[headerIndexByName["发行市盈率"]]
        document["Industry_PE_TTM"] = row_data[headerIndexByName["行业PE(近1月,TTM)"]]
        document["Issue_Volume"] = row_data[headerIndexByName["总计"]]
        document["Issue_Amount_Fore"] = row_data[headerIndexByName["预计募资(上市公司)"]]
        document["Issue_Amount"] = row_data[headerIndexByName["募资总额(上市公司)"]]
        document["CSRC_Industry"] = row_data[headerIndexByName["证监会行业(2012版)"]]
        document["Wind_Industry"] = row_data[headerIndexByName["Wind行业"]]
        #
        document["Date"] = document["Announce_Date"]
        document["DateTime"] = document["Announce_Date"]
        document["Key2"] = document["Symbol"] + "_" + Gadget.ToDateString(document["Announce_Date"])

        for k , v in document.items():
            if v == "":
                document[k] = None
        #
        # print(document["Symbol"], document["Announce_Date"])
        # database.Upsert("stock", "IPO", [], document)
        # a = 0
        documents.append(document)
        if len(documents) > 100:
            database.Upsert_Many("stock", "IPO", [], documents)
            documents.clear()
        #
    database.Upsert_Many("stock", "IPO", [], documents)
    a = 0


def Load_SEO(database, pathFilename):
    # df_ipo = pd.read_csv(pathFilename, engine="python", header=0).fillna(0) # encoding='gbk',
    # print(df_ipo)
    # date_trans = list(stock_position['Date'])

    wb = xlrd.open_workbook(filename=pathFilename)
    sheet = wb.sheet_by_index(0)  # 通过索引获取表格
    #
    nrows = sheet.nrows
    ncols = sheet.ncols
    documents = []
    headerIndexByName = {}
    for i in range(1, nrows):
        row_data = sheet.row_values(i)
        content = row_data
        print(content)
        #
        if i == 1:
            headerCount = 0
            for header in content:
                header = header.strip(" ")
                header = header.strip("\n")
                headerIndexByName[header] = headerCount
                headerCount = headerCount + 1
            continue
        #
        if content[0] == "":
            break
        #
        document = {}
        document["Symbol"] = row_data[headerIndexByName["代码"]]
        document["Name"] = row_data[headerIndexByName["名称"]]
        # t = row_data[headerIndexByName["招股日期"]]
        # et = Gadget.ParseExcelDatetime(t)
        document["Announce_Date"] = Gadget.ParseExcelDatetime(row_data[headerIndexByName["股东大会公告日"]]).date()
        document["Issue_Date"] = Gadget.ParseExcelDatetime(row_data[headerIndexByName["发行日期"]]).date()
        document["GoPublic_Date"] = Gadget.ParseExcelDatetime(row_data[headerIndexByName["定增股份上市日"]]).date()
        document["UnRestrict_Date"] = Gadget.ParseExcelDatetime(row_data[headerIndexByName["限售解禁日"]]).date()
        document["SEO_Price"] = row_data[headerIndexByName["发行价格"]]
        document["Issue_Volume"] = row_data[headerIndexByName["增发数量(万股)"]]
        document["Issue_Amount_Fore"] = row_data[headerIndexByName["预计募集资金总额(亿元)"]]
        document["Issue_Amount"] = row_data[headerIndexByName["实际募资总额(亿元)"]]
        document["Is_Private"] = True
        document["Payment"] = row_data[headerIndexByName["认购方式"]]
        document["MajorShareHolder_Subscribe"] = row_data[headerIndexByName["大股东认购比例(%)"]]
        document["CSRC_Industry"] = row_data[headerIndexByName["证监会行业"]]
        document["Wind_Industry"] = row_data[headerIndexByName["Wind行业"]]
        #
        document["Date"] = document["Announce_Date"]
        document["DateTime"] = document["Announce_Date"]
        document["Key2"] = document["Symbol"] + "_" \
                           + Gadget.ToDateString(document["Announce_Date"]) + "_" \
                           + Gadget.ToDateString(document["Issue_Date"]) + "_" + str(document["SEO_Price"])
        # 仍然有少量重复

        for k , v in document.items():
            if v == "":
                document[k] = None
        #
        # print(document["Symbol"], document["Announce_Date"])
        # database.Upsert("stock", "IPO", [], document)
        # a = 0
        documents.append(document)
        if len(documents) > 100:
            database.Upsert_Many("stock", "SEO", [], documents)
            documents.clear()
        #
    database.Upsert_Many("stock", "SEO", [], documents)
    a = 0


def Load_UnRestrict(database, pathFilename):
    # df_ipo = pd.read_csv(pathFilename, engine="python", header=0).fillna(0) # encoding='gbk',
    # print(df_ipo)
    # date_trans = list(stock_position['Date'])

    wb = xlrd.open_workbook(filename=pathFilename)
    sheet = wb.sheet_by_index(0)  # 通过索引获取表格
    #
    nrows = sheet.nrows
    ncols = sheet.ncols
    documents = []
    headerIndexByName = {}
    for i in range(1, nrows):
        row_data = sheet.row_values(i)
        content = row_data
        print(content)
        #
        if i == 1:
            headerCount = 0
            for header in content:
                header = header.strip(" ")
                header = header.strip("\n")
                # 处理重复表头
                if header in headerIndexByName.keys():
                    temp = header.split("_")
                    if len(temp) > 1:
                        n = float(temp[1]) + 1
                        header = header + "_" + n
                    else:
                        header = header + "_1"
                headerIndexByName[header] = headerCount
                headerCount = headerCount + 1
            continue
        #
        if content[0] == "":
            break
        #
        document = {}
        document["Symbol"] = row_data[headerIndexByName["代码"]]
        document["Name"] = row_data[headerIndexByName["简称"]]
        document["UnRestrict_Date"] = Gadget.ParseExcelDatetime(row_data[headerIndexByName["解禁日期"]]).date()
        # document["Price"] = row_data[headerIndexByName["发行价格"]] # 解禁日价格
        document["UnRestrict_Volume"] = row_data[headerIndexByName["解禁数量(万股)"]]
        document["Share_Type"] = row_data[headerIndexByName["解禁股份类型"]]
        #
        document["TotalShares"] = row_data[headerIndexByName["总股本"]]
        document["CircShares_Before"] = row_data[headerIndexByName["流通A股"]]
        document["CircShares_After"] = row_data[headerIndexByName["流通A股_1"]]
        document["CircRatio_Before"] = row_data[headerIndexByName["占比(%)"]]
        document["CircRatio_After"] = row_data[headerIndexByName["占比(%)_1"]]
        #
        document["CSRC_Industry"] = row_data[headerIndexByName["证监会行业"]]
        document["Wind_Industry"] = row_data[headerIndexByName["Wind行业"]]
        #
        document["Date"] = document["UnRestrict_Date"]
        document["DateTime"] = document["UnRestrict_Date"]
        document["Key2"] = document["Symbol"] + "_" \
                           + Gadget.ToDateString(document["UnRestrict_Date"])

        for k , v in document.items():
            if v == "":
                document[k] = None
        #
        # print(document["Symbol"], document["Announce_Date"])
        # database.Upsert("stock", "IPO", [], document)
        # a = 0
        documents.append(document)
        if len(documents) > 100:
            database.Upsert_Many("stock", "UnRestrict", [], documents)
            documents.clear()
        #
    database.Upsert_Many("stock", "UnRestrict", [], documents)
    a = 0


# 手动更新
def Load_Mix(database, pathFilename):
    mix = pd.read_csv('inputdata/mix.csv', encoding='gbk',header=0).fillna(0)
    date_trans = list(mix['时间'])
    for i in range(len(date_trans)):
        date_trans[i] = datetime.datetime.strptime(date_trans[i],'%Y-%m-%d')
    mix['trade_date'] = date_trans
    mix.set_index(['trade_date'], inplace=True)

    SaveBigFactorToDatabase(mix['热度-低经验'], 'WatchMkt_Entry', "mix_questionary")
    SaveBigFactorToDatabase(mix['热度-高经验'], 'WatchMkt_Exp', "mix_questionary")
    SaveBigFactorToDatabase(mix['热度指数'], 'WatchMkt_Index', "mix_questionary")
    SaveBigFactorToDatabase(mix['信心指数'], 'Confidence_Index', "mix_questionary")
    SaveBigFactorToDatabase(mix['热度指数'], 'WatchMkt_Index', "mix_questionary")
    SaveBigFactorToDatabase(mix['短期情绪'], 'Sentiment_ST', "mix_questionary")
    SaveBigFactorToDatabase(mix['中期情绪'], 'Sentiment_LT', "mix_questionary")
    SaveBigFactorToDatabase(mix['期情bias'], 'Sentiment_LSDiff', "mix_questionary")
    SaveBigFactorToDatabase(mix['仓位指数'], 'HoldingLevel_Index', "mix_questionary")
    SaveBigFactorToDatabase(mix['不恐慌'], 'NoPanic_Index', "mix_questionary")

    print(mix)


if __name__ == '__main__':
    #
    datetime1 = datetime.datetime(2000, 1, 1)
    datetime2 = datetime.datetime(2019, 12, 31)
    # Load_IPO(database, pathFilename="d://data//timingModel//input//新股发行资料.xlsx")
    # Save_IPO(database, datetime1, datetime2)

    # Load_SEO(database, pathFilename="d://data//timingModel//input//定向增发发行资料.xlsx")
    # Save_SEO(database, datetime1, datetime2)

    datetime1 = datetime.datetime(2010, 2, 1)
    datetime2 = datetime.datetime(2019, 12, 31)
    # Load_UnRestrict(database, pathFilename="d://data//timingModel//input//限售股解禁公司明细.xlsx")
    # Save_UnRestrict(database, datetime1, datetime2)

    # Load_Mix(database, pathFilename='inputdata/mix.csv')
